package vip.xiaonuo.smzq.modular.nbfb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.kcl.entity.Zycl;
import vip.xiaonuo.smzq.modular.nbfb.entity.vo.NbfbTwoVo;

import java.util.List;

@Mapper
public interface ZyclTwoMapper extends BaseMapper<Zycl> {

    @Select("-- 合并后，增加小节\n" +
            "with base as \n" +
            "(select ks.nd, max(ks.xzqdm) xzqdm, ks.kqbh, ks.kcmc, \n" +
            "        case when ks.kcdm in (42201, 42202, 32006, 63701) then '' else ks.jsdxmc end jsdxmc, ks.kcjdm, zh.dm, zh.mc,ks.kcdm, \n" +
            "        case when ks.kcdm in (42201, 42202, 32006, 63701) then 0 else ks.tjdx end tjdx\n" +
            "from hisi_kshz ks\n" +
            "left join hisi_ks_zyclhz zy\n" +
            "on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
            "left join kcl_cb06_kc kc on ks.kcdm=kc.kcdm and ks.tjdx = kc.tjdx\n" +
            "  left join kcl_cb01_zh zh on kc.kclb::int8/10*100 = zh.dm::int8\n" +
            "where ks.nd = #{nd} and ks.sbbsm<>3 and zy.zycllx IN ('TM', 'KZ', 'TD')\n" +
            "group by ks.nd, ks.kqbh, ks.kcmc, ks.jsdxmc, ks.kcjdm, zh.dm, zh.mc, ks.kcdm, ks.tjdx),\n" +
            "\n" +
            "data as(\n" +
            "select CASE \n" +
            "            WHEN mc IS NULL OR mc = '' THEN '700400' \n" +
            "            ELSE dm \n" +
            "        END AS dm, \n" +
            "        CASE \n" +
            "            WHEN mc IS NULL OR mc = '' THEN '贵金属、稀有分散元素矿产' \n" +
            "            ELSE mc \n" +
            "        END AS mc,  kcdm, kcmc, tjdx, jsdxmc, \n" +
            "    sum(case when kcjdm =101 then 1 else 0 end) pc, \n" +
            "    sum(case when kcjdm =102 then 1 else 0 end) xc, \n" +
            "    sum(case when kcjdm =103 then 1 else 0 end) kt,\n" +
            "    count (kqbh) zs\n" +
            "     from base\n" +
            "group by dm, mc, kcdm, kcmc, tjdx, jsdxmc\n" +
            "order by kcdm, tjdx\n" +
            "),\n" +
            "\n" +
            "xj as (\n" +
            "select dm, mc, 99999 kcdm,'小计' kcmc, 0 tjdx, '' jsdxmc, sum(pc) pc, sum(xc) xc, sum(kt) kt, sum(zs) zs from data \n" +
            "group by dm, mc\n" +
            "),\n" +
            "\n" +
            "zj as (\n" +
            "select 9999999 dm, ''mc, 199999 kcdm,'总计' kcmc, 0 tjdx, '' jsdxmc, sum(pc) pc, sum(xc) xc, sum(kt) kt, sum(zs) zs from data \n" +
            ")\n" +
            "\n" +
            "select mc as kl,  (case when jsdxmc <> '' then CONCAT(kcmc, ' (', jsdxmc, ')') else kcmc end) as kcmc, pc, xc, kt, zs as zj from (\n" +
            "select * from data\n" +
            "union\n" +
            "select * from xj\n" +
            "union\n" +
            "select * from zj\n" +
            ") T\n" +
            "order by dm, kcdm, tjdx")
    List<NbfbTwoVo> getNbfbTwo(Integer nd);
}
